{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7c2b258e",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/peremartra/Large-Language-Model-Notebooks-Course/blob/main/1-Introduction%20to%20LLMs%20with%20OpenAI/1_2-Easy_NL2SQL.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "MDJ3_BRhasUG",
   "metadata": {
    "id": "MDJ3_BRhasUG"
   },
   "source": [
    "<div>\n",
    "<h1>Large Language Models Projects</h1>\n",
    "    <h3>Apply and Implement Strategies for Large Language Models</h3>\n",
    "    <h2>1.2-Create a simple Natural Language to SQL using OpenAI</h2>\n",
    "    \n",
    "</div>\n",
    "\n",
    "by [Pere Martra](https://www.linkedin.com/in/pere-martra/)\n",
    "<hr>\n",
    "\n",
    "Models: gpt-3.5-turbo / gpt-4o-mini\n",
    "\n",
    "Colab Environment: CPU\n",
    "\n",
    "Keys:\n",
    "* NL2SQL\n",
    "* Code Generation\n",
    "* Prompt Hardening.\n",
    "\n",
    "Related article: [How to Create a Natural Language to SQL Translator Using OpenAI API](https://pub.towardsai.net/how-to-create-a-natural-language-to-sql-translator-using-openai-api-e1b1f72ac35a)\n",
    "____________________\n",
    "This is the unofficial repository for the book:\n",
    "        <a href=\"https://amzn.to/4eanT1g\"> <b>Large Language Models:</b> Apply and Implement Strategies for Large Language Models</a> (Apress).\n",
    "        The book is based on the content of this repository, but the notebooks are being updated, and I am incorporating new examples and chapters.\n",
    "        If you are looking for the official repository for the book, with the original notebooks, you should visit the\n",
    "        <a href=\"https://github.com/Apress/Large-Language-Models-Projects\">Apress repository</a>, where you can find all the notebooks in their original format as they appear in the book."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c23b4dd1",
   "metadata": {
    "id": "c23b4dd1"
   },
   "source": [
    "# SQL Generator\n",
    "A sample of how to build a translator from natural language to SQL:\n",
    "\n",
    "* GPT 35 / gpt-4o-mini\n",
    "* OpenAI\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1d00720",
   "metadata": {
    "id": "a1d00720"
   },
   "outputs": [],
   "source": [
    "!pip install -q openai==1.1.1\n",
    "!pip install -q panel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a03f026a",
   "metadata": {
    "id": "a03f026a"
   },
   "outputs": [],
   "source": [
    "import openai\n",
    "import panel as pn\n",
    "openai.api_key=\"your-openai-key\"\n",
    "#model = \"gpt-3.5-turbo\"\n",
    "model = \"gpt-4o-mini\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77eac86d",
   "metadata": {
    "id": "77eac86d"
   },
   "outputs": [],
   "source": [
    "def continue_conversation(messages, temperature=0):\n",
    "    response = openai.chat.completions.create(\n",
    "        model=model,\n",
    "        messages=messages,\n",
    "        temperature=temperature,\n",
    "    )\n",
    "    #print(str(response.choices[0].message[\"content\"]))\n",
    "    return response.choices[0].message.content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51bec475",
   "metadata": {
    "id": "51bec475"
   },
   "outputs": [],
   "source": [
    "def add_prompts_conversation(_):\n",
    "    #Get the value introduced by the user\n",
    "    prompt = client_prompt.value_input\n",
    "    client_prompt.value = ''\n",
    "\n",
    "    #Append to the context the User promnopt.\n",
    "    context.append({'role':'user', 'content':f\"{prompt}.\"})\n",
    "    context.append({'role':'system', 'content':f\"\"\"Only return SQL Orders.\n",
    "    If you can't return and SQL order, say sorry, and ask, politely but concisely, for a new question.\"\"\"})\n",
    "\n",
    "    #Get the response.\n",
    "    response = continue_conversation(context)\n",
    "\n",
    "    #Add the response to the context.\n",
    "    context.append({'role':'assistant', 'content':f\"{response}\"})\n",
    "\n",
    "    #Undate the panels to shjow the conversation.\n",
    "    panels.append(\n",
    "        pn.Row('User:', pn.pane.Markdown(prompt, width=600)))\n",
    "    panels.append(\n",
    "        pn.Row('Assistant:', pn.pane.Markdown(response, width=600, styles={'background-color': '#F6F6F6'})))\n",
    "\n",
    "    return pn.Column(*panels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "922f8d24",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17
    },
    "id": "922f8d24",
    "outputId": "cc5bf6a2-08a9-4c9c-80ff-b6bbf3fc9194"
   },
   "outputs": [],
   "source": [
    "context = [ {'role':'system', 'content':\"\"\"\n",
    "you are a bot to assist in create SQL commands, all your answers should start with\n",
    "this is your SQL, and after that an SQL that can do what the user request.\n",
    "\n",
    "Your SQL Database is composed by some tables.\n",
    "Try to Maintain the SQL order simple.\n",
    "Just after the SQL add a simple and concise text explaining how it works.\n",
    "If the user ask for something that can not be solved with an SQL Order\n",
    "just answer something nice and simple, maximum 10 words, asking him for something that\n",
    "can be solved with SQL.\n",
    "\"\"\"} ]\n",
    "\n",
    "context.append( {'role':'system', 'content':\"\"\"\n",
    "first table:\n",
    "{\n",
    "  \"tableName\": \"employees\",\n",
    "  \"fields\": [\n",
    "    {\n",
    "      \"nombre\": \"ID_usr\",\n",
    "      \"tipo\": \"int\"\n",
    "    },\n",
    "    {\n",
    "      \"nombre\": \"name\",\n",
    "      \"tipo\": \"string\"\n",
    "    }\n",
    "  ]\n",
    "}\n",
    "\"\"\"\n",
    "})\n",
    "\n",
    "context.append( {'role':'system', 'content':\"\"\"\n",
    "second table:\n",
    "{\n",
    "  \"tableName\": \"salary\",\n",
    "  \"fields\": [\n",
    "    {\n",
    "      \"nombre\": \"ID_usr\",\n",
    "      \"type\": \"int\"\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"year\",\n",
    "      \"type\": \"date\"\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"salary\",\n",
    "      \"type\": \"float\"\n",
    "    }\n",
    "  ]\n",
    "}\n",
    "\"\"\"\n",
    "})\n",
    "\n",
    "context.append( {'role':'system', 'content':\"\"\"\n",
    "third table:\n",
    "{\n",
    "  \"tablename\": \"studies\",\n",
    "  \"fields\": [\n",
    "    {\n",
    "      \"name\": \"ID\",\n",
    "      \"type\": \"int\"\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"ID_usr\",\n",
    "      \"type\": \"int\"\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"educational level\",\n",
    "      \"type\": \"int\"\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"Institution\",\n",
    "      \"type\": \"string\"\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"Years\",\n",
    "      \"type\": \"date\"\n",
    "    }\n",
    "    {\n",
    "      \"name\": \"Speciality\",\n",
    "      \"type\": \"string\"\n",
    "    }\n",
    "  ]\n",
    "}\n",
    "\"\"\"\n",
    "})\n",
    "\n",
    "#Creating the panel.\n",
    "pn.extension()\n",
    "\n",
    "panels = []\n",
    "\n",
    "client_prompt = pn.widgets.TextInput(value=\"Hi\", placeholder='Order your data…')\n",
    "button_conversation = pn.widgets.Button(name=\"generate SQL\")\n",
    "\n",
    "interactive_conversation = pn.bind(add_prompts_conversation, button_conversation)\n",
    "\n",
    "dashboard = pn.Column(\n",
    "    client_prompt,\n",
    "    pn.Row(button_conversation),\n",
    "    pn.panel(interactive_conversation, loading_indicator=True),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88db4691",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 512
    },
    "id": "88db4691",
    "outputId": "cd2aa205-0bab-4d13-e3f8-f83087ea6807"
   },
   "outputs": [],
   "source": [
    "#Sample question: \"Return the name of the best paid employee\"\n",
    "dashboard"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "include_colab_link": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
